package com.sms.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.sms.dao.IUserInfoSmsDAO;
import com.sms.db.DBOperator;
import com.sms.po.UserInfoSms;


public class UserInfoSmsDAO extends HibernateDaoSupport implements IUserInfoSmsDAO {
	private static final Log log = LogFactory.getLog(UserInfoSmsDAO.class);

	@Override
	public boolean addUserInfoSms(UserInfoSms uis) {
		log.debug("add userinfosms instance..");
		try{
			this.getHibernateTemplate().save(uis);
			return true;
		}catch(Exception re){
			log.error("add userinfosms instance failed:",re);
			re.printStackTrace();
		}
		return false;
	}

	@SuppressWarnings("unchecked")
	@Override
	public UserInfoSms findById(String id) {
		log.debug("find userinfosms");
		try{
			List<UserInfoSms> list =  this.getHibernateTemplate().find("from UserInfoSms where userId = ?",id);
			return list == null || list.size() ==0 ? null : list.get(0);
		}catch(RuntimeException re){
			log.error("find userinfosms by id="+id+" failed:",re);
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public UserInfoSms findByIdAndPass(String id, String pass) {
		log.debug("login user:"+id+" pass:"+pass.substring(1,1)+"****");
		try{
			List<UserInfoSms> list =  this.getHibernateTemplate().find("from UserInfoSms where userId = ? and password = ?",new String[] {id,pass});
			return list == null || list.size() ==0 ? null : list.get(0);
		}catch(RuntimeException re){
			log.error("login user:"+id+" pass:"+pass.substring(1,1)+"**** failed:",re);
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UserInfoSms> findByMap(final Map<String, Object> map,final int start,final int limit) {
		log.debug("find userInfoSms by map:"+map);
		try{
			return (List<UserInfoSms>) this.getHibernateTemplate().execute(new HibernateCallback(){
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					List<UserInfoSms> l = null;
					try {
						StringBuffer sql = new StringBuffer("from UserInfoSms where 1=1 ");
						if(map.get("createUser") != null){
							sql.append(" and (createUserid = :createUser or parentId = :parentId ");
						}
						if(map.get("userId") !=null){
							if(map.get("createUser") != null)
								sql.append(") ");
							sql.append(" and userId like :userId ");
						}else if(map.get("createUser") != null){
							sql.append(" or userId = :currentUserId )");
						}
						
						
						if(map.get("mobile" )!= null){
							sql.append(" and cellphone like :mobile");
						}
						if(map.get("userName" )!= null){
							sql.append(" and userName like :userName");
						}
						Query q = session.createQuery(sql.toString());
						
						if(map.get("createUser") != null){
							q.setString("createUser", map.get("createUser").toString());
							q.setString("parentId", map.get("createUser").toString());
						}
						if(map.get("userId") != null){
							q.setString("userId", "%"+map.get("userId").toString()+"%");
						}else if(map.get("createUser") != null){
							q.setString("currentUserId", map.get("createUser").toString());
						}
						if(map.get("mobile" )!= null){
							q.setString("mobile", "%"+map.get("mobile").toString()+"%");
						}
						if(map.get("userName" )!= null){
							q.setString("userName", "%"+map.get("userName").toString()+"%");
						}
						q.setFirstResult(start);
						q.setMaxResults(limit);
						l = q.list();
					} catch (Exception e) {
						e.printStackTrace();
					}finally{
						session.close();
					}
					return l;
				}
				
			});
		}catch(RuntimeException re){
			log.error("find userInfoSms by map:"+map +" failed:",re);
			throw re;
		}
	}

	@Override
	public int findByMapCount(final Map<String, Object> map) {
		log.debug("find userInfoSms by map:" + map);
		try {
			return Integer.valueOf(this.getHibernateTemplate()
					.execute(new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							List l = null;;
							try {
								StringBuffer sql = new StringBuffer(
										"select count(*) from UserInfoSms where 1=1 ");
								if (map.get("createUser") != null) {
									sql
											.append(" and (createUserid = :createUser or parentId = :parentId) ");
								}
								if (map.get("userId") != null) {
									sql.append(" and userId like :userId ");
								}
								if (map.get("mobile") != null) {
									sql.append(" and cellphone like :mobile");
								}
								if (map.get("userName") != null) {
									sql.append(" and userName like :userName");
								}
								Query q = session.createQuery(sql.toString());

								if (map.get("createUser") != null) {
									q.setString("createUser", map.get("createUser")
											.toString());
									q.setString("parentId", map.get("createUser")
											.toString());
								}
								if (map.get("userId") != null) {
									q.setString("userId", "%"
											+ map.get("userId").toString() + "%");
								}
								if (map.get("mobile") != null) {
									q.setString("mobile", "%"
											+ map.get("mobile").toString() + "%");
								}
								if (map.get("userName") != null) {
									q.setString("userName", "%"
											+ map.get("userName").toString() + "%");
								}
								l = q.list();
							} catch (Exception e) {
								e.printStackTrace();
							}finally{
								session.close();
							}
							return l.get(0);
						}

					}).toString());
		} catch (RuntimeException re) {
			log.error("find userInfoSms by map:" + map + " failed:", re);
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UserInfoSms> findByType(final String type, final int start, final int limit) {
		log.debug("find userinfosms by type:"+type+",split page from "+start +" to "+ limit);
		try{
			return (List<UserInfoSms>) this.getHibernateTemplate().execute(new HibernateCallback(){
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					List<UserInfoSms> l = null;
					try {
						Query query = session
								.createQuery("from UserInfoSms where userType = ? ");
						query.setParameter(1, type);
						query.setFetchSize(start);
						query.setMaxResults(limit);
						l = query.list();
					} catch (Exception e) {
						e.printStackTrace();
					}finally{
						session.close();
					}
					return l;
				}
				
			});
		}catch(RuntimeException re){
			log.error("find userinfosms by type:"+type+",split page from "+start +" to "+ limit,re);
			throw re;
		}
	}

	@Override
	public int findByTypeCount(final String type) {
		log.debug("find userinfosms by type:"+type+" count");
		try{
			return (Integer) this.getHibernateTemplate().find("select count(*) from UserInfoSms where userType = ?", type).get(0);
		}catch(RuntimeException re){
			log.error("find userinfosms by type:"+type+" count");
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UserInfoSms> findSubUserById(final String user_id,final int start,final int limit) {
		log.debug("find sub userinfosms by id:"+user_id+",split page from "+start +" to "+ limit);
		try{
			return (List<UserInfoSms>) this.getHibernateTemplate().execute(new HibernateCallback(){
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					List<UserInfoSms> l = null;
					try {
						Query query = session.createQuery("from UserInfoSms where parentId = ? ");
						query.setParameter(1, user_id);
						query.setFetchSize(start);
						query.setMaxResults(limit);
						l = query.list();
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally{
						session.close();
					}
					return l;
				}
				
			});
		}catch(RuntimeException re){
			log.error("find sub userinfosms by id:"+user_id+",split page from "+start +" to "+ limit,re);
			throw re;
		}
	}

	@Override
	public int findSubUserByIdCount(String user_id) {
		log.debug("find sub userinfosms by id:"+user_id+" count");
		try{
			return (Integer) this.getHibernateTemplate().find("select count(*) from UserInfoSms where parentId = ?", user_id).get(0);
		}catch(RuntimeException re){
			log.error("find sub userinfosms by id:"+user_id+" count");
			throw re;
		}
	}

	@Override
	public boolean removeUserInfoSms(UserInfoSms uis) {
		log.debug("remove userinfosms instance..");
		try{
			this.getHibernateTemplate().delete(uis);
			return true;
		}catch(RuntimeException re){
			log.error("remove userinfosms instance failed:",re);
			throw re;
		}
	}

	@Override
	public boolean removeUserInfoSmsById(final String ui) {
		log.debug("add userinfosms instance by id:"+ui);
		try{
			return (Boolean) this.getHibernateTemplate().execute(new HibernateCallback(){
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					try {
						session.delete("delete from UserInfoSms where userId = ? ", ui);
					} catch (Exception e) {
						e.printStackTrace();
						return false;
					}finally{
						session.close();
					}
					return true;
				}
			});
			
		}catch(RuntimeException re){
			log.error("add userinfosms instance by id:"+ui+" failed:",re);
			throw re;
		}
	}

	@Override
	public boolean updateUserInfoSms(UserInfoSms uis) {
		log.debug("update userinfosms instance..");
		try{
			this.getHibernateTemplate().update(uis);
			return true;
		}catch(RuntimeException re){
			log.error("update userinfosms instance failed:",re);
			throw re;
		}
	}

	@Override
	public List<String> findSubUserIds(String parentUserId) {
		String sql="SELECT user_id FROM user_info_sms WHERE parent_id='"+parentUserId+"'";
		List<String> list=new ArrayList<String>();
		Connection conn = DBOperator.getConnection();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				list.add(rs.getString(1));
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
			return list;
		}finally{
			DBOperator.closeConnection(conn);
		}
		return list;
	}
}